﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Web;

namespace QuocHoi_TV_Frontend.Models
{
    public class DataModels
    {
        private static QuocHoi_TVEntities db = new QuocHoi_TVEntities();

        #region QuocHoi_TV News
        public static List<News> GetHotNews(int top)
        {
            try
            {
                List<News> result = new List<News>();
                List<News> listlive = getnewsLive(0);
                var itemIds = listlive.Select(x => x.NewsID).ToArray();
                int topsub = 0;
                if (listlive != null)
                {
                    if (top > listlive.Count)
                    {
                        topsub = top - listlive.Count;
                        List<News> query = (from p in db.News.AsNoTracking()
                                            where p.IsActive == true && p.IsHot == true
                                                && !itemIds.Contains(p.NewsID)
                                            orderby p.NewsID descending
                                            select p).Take(topsub).ToList();
                        result = mergelist(listlive, query);
                    }
                    else
                    {
                        topsub = top;
                        listlive = getnewsLive(topsub);
                        result = listlive;
                    }
                }
                else
                {
                    List<News> query = (from p in db.News.AsNoTracking()
                                        where p.IsActive == true && p.IsHot == true
                                        orderby p.NewsID descending
                                        select p).Take(topsub).ToList();
                    result = query;
                }
                return result;
            }
            catch (Exception)
            {
                return null;
            }

        }
        public static List<News> GetLastestNews(int top)
        {
            try
            {
                var query = (from p in db.News.AsNoTracking() where p.IsActive == true orderby p.NewsID descending select p).Take(top).ToList();
                return query;
            }
            catch (Exception)
            {

                return null;
            }
        }
        public static List<News> GetVideoNews(int top)
        {
            try
            {
                var query = (from p in db.News.AsNoTracking() where p.IsActive == true && p.TypeNews == 1 orderby p.NewsID descending select p).Take(top).ToList();
                return query;
            }
            catch (Exception)
            {

                return null;
            }
        }
        public static List<Category> GetAllSport()
        {
            try
            {
                var query = (from p in db.Categories.AsNoTracking() where p.IsActive == true && p.BitType == 1 orderby p.OrderPos descending select p).ToList();
                return query;
            }
            catch (Exception)
            {

                return null;
            }
        }
        public static List<Category> GetCateChildlevel1(long cateid,int top)
        {
            try
            {
                List<Category> query = (from p in db.Categories.AsNoTracking() where p.ParentId== cateid select p).Take(top).ToList();
                return query;
            }
            catch (Exception)
            {
                return null;
            }
        }
        public static List<Category> GetCateHaveNews(int top)
        {
            var CatehaveNews = from b in db.News
                               group b by b.CateId into grp
                               where grp.Count() > 1
                               select grp.Key;

            var CateNews = (from p in db.Categories
                            where p.BitVertical == false
                                    && p.ParentId == 0
                                    && p.IsActive == true
                                    && p.LanguageID == 1
                                    && CatehaveNews.Contains(p.CateID)
                            select p).Take(top).ToList();
            return CateNews;
        }
        public static List<Category> getallcateidchild(long catid, List<Category> lstRe, bool ireturn = false)
        {
            try
            {
                if (!ireturn)
                {
                    Category cat = (from p in db.Categories.AsNoTracking() where p.CateID == catid select p).Single();

                    lstRe.Add(cat);
                }

                var catparent = (from p in db.Categories.AsNoTracking() where p.ParentId == catid select p).ToList();
                foreach (Category item in catparent)
                {
                    lstRe.Add(item);
                    var catparentchild = (from p in db.Categories.AsNoTracking() where p.ParentId == item.CateID select p).ToList();
                    if (catparentchild != null)
                    {
                        getallcateidchild(item.CateID, lstRe, true);
                    }
                }
                return lstRe;
            }
            catch (Exception)
            {

                return null;
            }
        }
        public static List<News> GetNewsRelate(long NewID, long CateID, int top)
        {
            try
            {
                var query = (from p in db.News where p.IsActive == true && p.NewsID != NewID && p.CateId == CateID orderby p.NewsID descending select p).Take(top).ToList();
                return query;
            }
            catch (Exception)
            {

                return null;
            }
        }
        public static Category SelectOne(long CateID)
        {
            try
            {
                if (CateID > 0)
                {
                    var query = db.Categories.Find(CateID);
                    return query;
                }
                else
                {
                    return null;
                }
            }
            catch (Exception)
            {

                return null;
            }

        }
        public static Category SelectOneByName(string cateName)
        {
            try
            {
                if (!String.IsNullOrEmpty(cateName))
                {
                    var query = (from p in db.Categories.AsNoTracking() where p.IsActive == true && p.CateNameUnsign.ToLower().Contains(cateName.ToLower()) select p).Single();
                    return query;
                }
                else
                {
                    return null;
                }
            }
            catch (Exception)
            {

                return null;
            }
        }
        public static List<News> GetNewsByCate(long catid, int top)
        {
            try
            {
                List<Category> lstcate = new List<Category>();
                lstcate = getallcateidchild(catid, lstcate, false);
                var cateids = lstcate.Select(s => s.CateID).ToList();
                var query = (from x in db.News.AsNoTracking()
                             where x.IsActive == true && cateids.Any(es => es == x.CateId)
                             orderby x.NewsID descending
                             select x).Take(top).ToList();
                return query;
            }
            catch (Exception)
            {

                return null;
            }
        }
        public static News SelectNewsOne(long newsID)
        {
            try
            {
                var lastestNews = db.News.Find(newsID);
;
                if (lastestNews.CountViews != null)
                {
                    lastestNews.CountViews = lastestNews.CountViews + 1;
                }
                else { lastestNews.CountViews = 1; }
                db.Entry(lastestNews).State = EntityState.Modified;
                db.SaveChanges();
                return lastestNews;
            }
            catch (Exception)
            {

                return null;
            }
        }
        public static List<NewsData> GetNewsData(long NewsID)
        {
            try
            {
                var data = (from c in db.NewsDatas.AsNoTracking() where c.NewsID == NewsID orderby c.NewsID descending select c).ToList();
                return data;
            }
            catch (Exception)
            {
                return null;
            }

        }
        public static List<News> GetListNewsInTagsHadNewsID(long Newsid, int top)
        {
            try
            {
                List<Tags_News> lstTagsNews = new List<Tags_News>();
                //xem news nay có tag nao
                var lstTags = (from p in db.Tags_News.AsNoTracking() where p.NewsId == Newsid select p.TagsId).ToList();
                // lay tất cả news có trong list tag
                var lstNewsTag = (from x in db.Tags_News.AsNoTracking()
                                  where lstTags.Any(es => es == x.TagsId)
                                  select x.NewsId).ToList();
                // lay tat ca cac news trong các tag
                var query = (from x in db.News.AsNoTracking()
                             where x.IsActive == true && lstNewsTag.Any(es => es == x.NewsID)
                             select x).Take(top).ToList();
                return query;
            }
            catch (Exception)
            {
                return null;
            }
        }
        public static List<News> GetNewsByTags(long tagsID, int top)
        {
            try
            {
                var news = from c in db.News
                           join t in db.Tags_News.AsNoTracking() on c.NewsID equals t.NewsId
                           where c.IsActive == true && t.TagsId == tagsID
                           select c;
                return news.ToList();
            }
            catch (Exception)
            {

                return null;
            }

        }
        public static List<Tag> SelectAllTagByNews(long NewsID, int top)
        {
            try
            {
                var tags = (from c in db.Tags
                            join t in db.Tags_News.AsNoTracking() on c.TagsID equals t.TagsId
                            where t.NewsId == NewsID
                            select c).Take(top).ToList();
                return tags.ToList();
            }
            catch (Exception)
            {
                return null;
            }
        }
        public static Tag SelectTagOne(long TagID)
        {
            try
            {
                Tag query = db.Tags.Find(TagID);
                if (query.CountTags != null)
                {
                    query.CountTags = query.CountTags + 1;
                }
                else { query.CountTags = 1; }
                db.Entry(query).State = EntityState.Modified;
                db.SaveChanges();
                return query;
            }
            catch (Exception)
            {
                return null;
            }
        }
        public static List<News> GetNewsByDate(DateTime date, int top)
        {
            try
            {
                var Newsbydate = db.News.Where(x =>
                      x.CreateDate.Value.Year == date.Year
                      && x.CreateDate.Value.Month == date.Month
                      && x.CreateDate.Value.Day == date.Day).ToList();
                return Newsbydate;
            }
            catch (Exception)
            {
                return null;
            }
          
        }
        public static List<News> GetCountestNews(int top)
        {
            try
            {
                var query = (from p in db.News.AsNoTracking() where p.IsActive == true orderby p.CountViews descending select p).Take(top).ToList();
                return query;
            }
            catch (Exception)
            {

                return null;
            }
        }
        public static List<News> GetListNewsInEventsHadNewsID(long Newsid, int top)
        {
            try
            {
                List<Event_News> lstEventNews = new List<Event_News>();
                //xem news nay có tag nao
                var lstEvent = (from p in db.Event_News.AsNoTracking() where p.NewsId == Newsid select p.EventId).ToList();
                // lay tất cả news có trong list tag
                var lstNewsTag = (from x in db.Event_News.AsNoTracking()
                                  where lstEvent.Any(es => es == x.EventId)
                                  select x.NewsId).ToList();
                // lay tat ca cac news trong các tag
                var query = (from x in db.News.AsNoTracking()
                             where x.IsActive == true && lstNewsTag.Any(es => es == x.NewsID)
                             select x).Take(top).ToList();
                return query;
            }
            catch (Exception)
            {
                return null;
            }
        }
        public static List<News> GetNewsByEvent(long EventID, int top)
        {
            try
            {
                var news = from c in db.News
                           join t in db.Event_News.AsNoTracking() on c.NewsID equals t.NewsId
                           where c.IsActive == true && t.EventId == EventID
                           select c;
                return news.ToList();
            }
            catch (Exception)
            {

                return null;
            }

        }
        #endregion

        #region QuocHoi_TV Telvision
        public static List<CommentNew> getCommentByNewsID(long newsID)
        {
            var comment = (from p in db.CommentNews where p.IsActive == true orderby p.DateCreate descending select p).ToList();
            return comment;
        }
        public static void PostComment(long newsID, string name, string email, string content)
        {
            CommentNew com = new CommentNew()
            {
                NewsID = newsID,
                ParentID = 0,
                UserName = name,
                Email = email,
                Contents = content,
                DateCreate = DateTime.Now,       
                IsActive = false,
            };
            db.CommentNews.Add(com);
            db.SaveChanges();
        }
        public static List<News> getTopVideo(int top)
        {
            var video = (from p in db.News where p.TypeNews == 1 && p.IsActive == true orderby p.CountViews descending select p).Take(top).ToList();
            return video;
        }

        public static List<BroadcastDetail> getBroadcastByDate(DateTime date)
        {
            try
            {
                var schedule = (from p in db.BroadcastDetails
                                join c in db.BroadcastSchedules on p.BroadcastID equals c.BroadcastID
                                where c.BroadcastDate.Value.Year == date.Year
                                      && c.BroadcastDate.Value.Month == date.Month
                                      && c.BroadcastDate.Value.Day == date.Day
                                select p).ToList();
                return schedule;
            }
            catch (Exception)
            {
                
                return null;
            }
           
        }
        public static List<BroadcastDetail> getBroadcastByDateSessionOfDate(DateTime date,TimeSpan tspfrom, TimeSpan tspto)
        {
            try
            {
                var schedule = (from p in db.BroadcastDetails
                                join c in db.BroadcastSchedules on p.BroadcastID equals c.BroadcastID
                                where c.BroadcastDate.Value.Year == date.Year
                                      && c.BroadcastDate.Value.Month == date.Month
                                      && c.BroadcastDate.Value.Day == date.Day
                                      && p.TimeFrom > tspfrom && p.TimeFrom < tspto
                                select p).ToList();
                return schedule;
            }
            catch (Exception)
            {

                return null;
            }
        }
        public static List<Category> getCateHaveVideo(int top)
        {
            var query = (from a in db.Categories
                         let af = db.News.Where(x => x.TypeNews == 1 && x.IsActive == true).Select(x => x.CateId)
                         where af.Contains(a.CateID)
                         select a).Take(top).ToList();
            return query;
        }

        public static List<Category> getCateHaveVideoByCateId(long cateId, int top)
        {
            var query = (from a in db.Categories
                         let af = db.News.Where(x => x.TypeNews == 1 && x.IsActive == true).Select(x => x.CateId)
                         where af.Contains(a.CateID) && a.CateID != cateId
                         select a).Take(top).ToList();
            return query;
        }

        public static List<News> getAllNews()
        {
            var query = (from p in db.News where p.IsActive == true orderby p.CreateDate descending select p).ToList();
            return query;
        }

        #endregion

        #region QuocHoi_TV Common
        public static List<News> getnewsLive(int top)
        {
            try
            {
                List<News> query = new List<News>();
                if (top == 0)
                {
                    query = (from p in db.News.AsNoTracking() where p.IsActive == true && p.IsSticky == true orderby p.StickyOrder descending select p).ToList();
                }
                else
                {
                    query = (from p in db.News.AsNoTracking() where p.IsActive == true && p.IsSticky == true orderby p.StickyOrder descending select p).Take(top).ToList();
                }
                return query;
            }
            catch (Exception)
            {
                return null;
            }

        }
        public static List<News> mergelist(List<News> list1, List<News> list2)
        {
            try
            {
                var allNews = new List<News>(list1.Count + list2.Count);
                allNews.AddRange(list1);
                allNews.AddRange(list2);
                return allNews.Distinct().ToList(); ;
            }
            catch (Exception)
            {
                return null;
            }
        }
        public static CacheSite GetCacheStatus()
        {
            CacheSite cache = (from u in db.CacheSites.AsNoTracking()
                               where u.StatusID == 1
                               select u).SingleOrDefault();

            return cache;
        }
        public static void UpdateCacheStatus(int id, int status)
        {
            CacheSite cache = db.CacheSites.Find(id);
            if (cache != null)
            {
                cache.IsCache = status;
                db.SaveChanges();
            }
        }
        #endregion
      

    }
}